﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections;





namespace Data.Database
{
    public class Turno
    {
        static private String cadenaConexion = ConfigurationManager.ConnectionStrings["Turnet"].ConnectionString; // @"Data Source=|DataDirectory|\BBDD.sdf";
        static private String nombreTabla = "Turnos";


        public void AnyadirTurno(DataSet turno)
        {
            DataSet dsTurno = new DataSet();
            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select * from Turnos";
                SqlDataAdapter daTurno = new SqlDataAdapter(consulta, conexion);
                daTurno.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daTurno.Fill(dsTurno, nombreTabla);
                SqlCommandBuilder cbuilder = new SqlCommandBuilder(daTurno);
                daTurno.Update(turno, "Turnos");

            }
            catch (Exception ex)
            {
                throw (ex);
            }
        }

        public DataTable ObtenerTurnosPaciente(string id_paciente)
        {
            DataTable dsTurnos = new DataTable();



            try
            {



                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "SELECT P.Apellido +' '+ P.Nombre AS Paciente,Pr.Apellido +' '+ Pr.Nombre AS Profesional,convert(varchar,T.Fecha,103) AS Fecha,convert(varchar,H.HoraIncio,108) AS Horario From Turnos AS T INNER JOIN Paciente AS P on P.Id_paciente=T.Id_paciente INNER JOIN Horario AS H ON H.Id_Horario=T.Id_Horario INNER JOIN Profesional AS Pr ON Pr.Id_Profesional=T.Id_Profesional Where T.Id_paciente='" + id_paciente + "' AND CONVERT(VARCHAR,T.FECHA,112) +' '+ CONVERT(VARCHAR,H.HoraIncio,108) >= CONVERT(VARCHAR,GETDATE(),112) +' '+ CONVERT(VARCHAR,GETDATE(),108) ORDER BY Convert(varchar,T.Fecha,112),Convert(varchar,H.HoraIncio,108)";
                SqlDataAdapter daTurnos = new SqlDataAdapter(consulta, conexion);
                daTurnos.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daTurnos.Fill(dsTurnos);






            }

            catch (Exception ex)
            {

            }

            return dsTurnos;


        }




        public DataSet ObtenerTablaTurno()
        {
            DataSet dsTurnos = new DataSet();

            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "SELECT * FROM Turnos";
                SqlDataAdapter daTurnos = new SqlDataAdapter(consulta, conexion);
                daTurnos.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daTurnos.Fill(dsTurnos, nombreTabla);
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            return dsTurnos;

        }

         public DataSet ObtenerTurnosPacienteConFecha(string id_paciente, string fecha, string horario)
        {
            DataSet dsTurnos = new DataSet();



            try
            {



                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "Select * from Turnos AS T INNER JOIN Paciente AS P ON P.Id_Paciente=T.Id_Paciente INNER JOIN Horario AS H ON H.Id_Horario=T.Id_Horario WHERE T.Id_Paciente='" + id_paciente + "' AND CONVERT(VARCHAR,T.fecha,103)='" + fecha + "' AND CONVERT(VARCHAR,H.HoraIncio,108)='" + horario + "'";
                SqlDataAdapter daTurnos = new SqlDataAdapter(consulta, conexion);
                daTurnos.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daTurnos.Fill(dsTurnos, "Turnos");






            }

            catch (Exception ex)
            {

            }

            return dsTurnos;
         }


        public void BajaTurno(string id_turno)
        {
            DataSet dsTurnos = new DataSet();
            try
            {
                SqlConnection conexion = new SqlConnection(cadenaConexion);
                String consulta = "DELETE FROM Turnos  WHERE Turnos.Id_Turno='"+id_turno+"'  ";
                SqlDataAdapter daTurnos = new SqlDataAdapter(consulta, conexion);
                daTurnos.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                daTurnos.Fill(dsTurnos, nombreTabla);
            }
            catch (Exception ex)
            {
                throw (ex);
            }


        }

       

















        }



    }

